# scripts/db_init.py
import sys
import os
from datetime import datetime
from sqlalchemy import create_engine, inspect, func
from sqlalchemy.orm import sessionmaker, joinedload
from backend.database import Base, engine, SessionLocal
from backend.models import User, Library, LibraryMember, Book, BorrowRecord
from backend.utils.security import get_password_hash
from sqlalchemy.exc import OperationalError
import texttable as tt  # 用于美化表格输出


# 安装依赖: pip install texttable
# 如果无法安装，使用标准打印方式

def print_table(title, headers, rows):
    """打印表格数据"""
    try:
        # 尝试使用texttable创建美观表格
        table = tt.Texttable()
        table.set_cols_align(["c"] * len(headers))
        table.set_cols_valign(["m"] * len(headers))
        table.header(headers)

        for row in rows:
            table.add_row(row)

        print(f"\n{title}")
        print(table.draw())
    except ImportError:
        # 回退到简单表格
        print(f"\n{title}")
        print(" | ".join(headers))
        print("-" * (sum(len(str(h)) for h in headers) + 3 * len(headers)))
        for row in rows:
            print(" | ".join(str(x) for x in row))


def clear_database():
    """完全清除数据库中的所有表和数据"""
    print("开始清除数据库...")

    # 获取所有表名
    inspector = inspect(engine)
    table_names = inspector.get_table_names()

    if not table_names:
        print("数据库中没有表，无需清除")
        return

    # 删除所有表
    Base.metadata.drop_all(bind=engine)
    print(f"已删除 {len(table_names)} 张表: {', '.join(table_names)}")

    # 确保表已删除
    remaining_tables = inspect(engine).get_table_names()
    if remaining_tables:
        print(f"警告: 仍有表存在: {', '.join(remaining_tables)}")
    else:
        print("数据库已完全清除")


def create_tables():
    """创建所有数据库表"""
    print("开始创建数据库表...")

    try:
        Base.metadata.create_all(bind=engine)

        # 验证创建的表
        inspector = inspect(engine)
        created_tables = inspector.get_table_names()
        expected_tables = [
            'users', 'libraries', 'library_members',
            'books', 'borrow_records'
        ]

        missing_tables = [t for t in expected_tables if t not in created_tables]

        if missing_tables:
            print(f"错误: 缺少表: {', '.join(missing_tables)}")
            return False

        print(f"成功创建 {len(created_tables)} 张表")
        return True
    except Exception as e:
        print(f"创建表失败: {str(e)}")
        import traceback
        traceback.print_exc()
        return False


def create_initial_data():
    """创建初始数据并验证结果"""
    print("开始创建初始数据...")

    db = SessionLocal()

    try:
        # 创建管理员
        admin_username = "ttest"
        admin_password = "we@test2025"

        # 使用安全模块的哈希函数
        from backend.utils.security import get_password_hash
        hashed_password = get_password_hash(admin_password)

        # 记录使用的哈希方法
        print(f"管理员账号: {admin_username}")
        print(f"管理员密码: {admin_password}")
        print(f"密码哈希: {hashed_password[:30]}... (截断显示)")

        admin = User(
            username=admin_username,
            hashed_password=hashed_password,
            full_name="管理员",
            is_admin=True,
            created_at=datetime.utcnow()
        )
        db.add(admin)
        print(f"创建管理员用户: {admin_username}")

        # 提交以获取ID
        db.flush()

        # 创建示例书阁
        library_name = "六年级一班共享书阁"
        library = Library(
            name=library_name,
            owner_id=admin.id,
            is_active=True,
            created_at=datetime.utcnow()
        )
        db.add(library)
        print(f"创建书阁: {library_name}")

        # 提交以获取ID
        db.flush()

        # 添加管理员到书阁
        member = LibraryMember(
            user_id=admin.id,
            library_id=library.id,
            is_owner=True,
            join_date=datetime.utcnow()
        )
        db.add(member)
        print("添加管理员到书阁")

        # 创建示例图书
        books = [
            {
                "isbn": "9787020002207",
                "title": "红楼梦",
                "author": "曹雪芹",
                "cover_url": "https://img1.doubanio.com/view/subject/s/public/s1070959.jpg"
            },
            {
                "isbn": "9787020008728",
                "title": "西游记",
                "author": "吴承恩",
                "cover_url": "https://img9.doubanio.com/view/subject/s/public/s1627374.jpg"
            },
            {
                "isbn": "9787020008742",
                "title": "水浒传",
                "author": "施耐庵",
                "cover_url": "https://img1.doubanio.com/view/subject/s/public/s1147854.jpg"
            },
            {
                "isbn": "9787020008735",
                "title": "三国演义",
                "author": "罗贯中",
                "cover_url": "https://img9.doubanio.com/view/subject/s/public/s1076932.jpg"
            }
        ]

        for book_info in books:
            book = Book(
                isbn=book_info["isbn"],
                title=book_info["title"],
                author=book_info["author"],
                cover_url=book_info["cover_url"],
                library_id=library.id,
                added_by=admin.id,
                status=1,  # 上架状态
                added_at=datetime.utcnow()
            )
            db.add(book)
            print(f"添加图书: {book_info['title']}")

        # 创建借阅记录
        borrow_records = [
            {"book_title": "红楼梦", "borrower": "小明", "status": 1},
            {"book_title": "西游记", "borrower": "小红", "status": 0},
        ]

        for record_info in borrow_records:
            book = db.query(Book).filter(Book.title == record_info["book_title"]).first()
            if book:
                record = BorrowRecord(
                    book_id=book.id,
                    borrower_id=admin.id,  # 使用管理员作为借阅者
                    status=record_info["status"],
                    borrow_date=datetime.utcnow()
                )
                db.add(record)
                print(f"创建借阅记录: {record_info['borrower']} 借阅 {record_info['book_title']}")

                # 如果是已借出状态，更新图书状态
                if record_info["status"] == 1:
                    book.status = 2  # 借出状态
                    db.add(book)

        # 提交所有更改
        db.commit()
        print("初始数据创建完成！")

        # === 数据验证部分 ===
        print("\n" + "=" * 50)
        print("数据库数据验证")
        print("=" * 50)

        # 1. 查询用户数据
        users = db.query(User).all()
        user_rows = []
        for user in users:
            user_rows.append([
                user.id,
                user.username,
                user.full_name,
                "是" if user.is_admin else "否",
                user.created_at.strftime("%Y-%m-%d %H:%M")
            ])

        print_table("用户表 (users)",
                    ["ID", "用户名", "姓名", "是否管理员", "创建时间"],
                    user_rows)

        # 2. 查询书阁数据
        libraries = db.query(Library).all()
        library_rows = []
        for lib in libraries:
            library_rows.append([
                lib.id,
                lib.name,
                lib.owner_id,
                "激活" if lib.is_active else "关闭",
                lib.created_at.strftime("%Y-%m-%d %H:%M")
            ])

        print_table("书阁表 (libraries)",
                    ["ID", "名称", "创建者ID", "状态", "创建时间"],
                    library_rows)

        # 3. 查询书阁成员
        members = db.query(LibraryMember).all()
        member_rows = []
        for mem in members:
            member_rows.append([
                mem.id,
                mem.user_id,
                mem.library_id,
                "是" if mem.is_owner else "否",
                mem.join_date.strftime("%Y-%m-%d %H:%M")
            ])

        print_table("书阁成员表 (library_members)",
                    ["ID", "用户ID", "书阁ID", "是否拥有者", "加入时间"],
                    member_rows)

        # 4. 查询图书数据
        books = db.query(Book).all()
        book_rows = []
        for book in books:
            status_map = {0: "下架", 1: "上架", 2: "借出"}
            book_rows.append([
                book.id,
                book.isbn,
                book.title[:15] + "..." if len(book.title) > 15 else book.title,
                book.author,
                status_map.get(book.status, "未知"),
                book.added_by,
                book.added_at.strftime("%Y-%m-%d %H:%M")
            ])

        print_table("图书表 (books)",
                    ["ID", "ISBN", "书名", "作者", "状态", "添加者ID", "添加时间"],
                    book_rows)

        # 5. 查询借阅记录
        borrows = db.query(BorrowRecord).all()
        borrow_rows = []
        for br in borrows:
            status_map = {0: "预约中", 1: "借出中", 2: "已归还"}
            borrow_rows.append([
                br.id,
                br.book_id,
                br.borrower_id,
                br.lender_id or "无",
                status_map.get(br.status, "未知"),
                br.borrow_date.strftime("%Y-%m-%d %H:%M"),
                br.return_date.strftime("%Y-%m-%d %H:%M") if br.return_date else "未归还"
            ])

        print_table("借阅记录表 (borrow_records)",
                    ["ID", "图书ID", "借阅者ID", "借出者ID", "状态", "借出时间", "归还时间"],
                    borrow_rows)

        # 6. 打印摘要统计
        user_count = db.query(User).count()
        library_count = db.query(Library).count()
        book_count = db.query(Book).count()
        borrow_count = db.query(BorrowRecord).count()

        print("\n数据库初始化摘要:")
        print(f"用户数量: {user_count}")
        print(f"书阁数量: {library_count}")
        print(f"图书数量: {book_count}")
        print(f"借阅记录: {borrow_count}")
        print(f"管理员账号: {admin_username}")
        print(f"管理员密码: {admin_password}")

        return True
    except Exception as e:
        db.rollback()
        print(f"创建初始数据失败: {str(e)}")
        import traceback
        traceback.print_exc()
        return False
    finally:
        db.close()


def main():
    """主初始化流程"""
    print("=" * 50)
    print("小学同学共享图书APP - 数据库初始化工具")
    print("=" * 50)
    print("警告: 此操作将完全清除数据库并重新初始化!")
    print("=" * 50)

    # 确认操作
    if input("确认继续? (y/n): ").lower() != "y":
        print("操作已取消")
        return

    # 清除数据库
    clear_database()

    # 创建表结构
    if not create_tables():
        print("表创建失败，终止初始化")
        return

    # 创建初始数据
    if not create_initial_data():
        print("数据初始化失败")
        return

    print("=" * 50)
    print("数据库初始化成功完成!")
    print("=" * 50)


if __name__ == "__main__":
    main()